-----------------------------------------------------------------------------
-- Configure AdventureWorks

USE AdventureWorks
GO

EXEC sp_configure 'clr enabled', '1'
GO

RECONFIGURE
GO

-----------------------------------------------------------------------------
-- User-Defined Functions
-----------------------------------------------------------------------------

-- clrSimpleFunction

SELECT dbo.clrSimpleFunction()

-- clrSimpleFunction assignment

DECLARE @Message nvarchar(50)
SELECT @Message = dbo.clrSimpleFunction()
PRINT @Message

-- CoolEncrypt

SELECT dbo.clrCoolEncrypt('SQL Server 2005 Rocks!')
SELECT dbo.clrCoolEncrypt('A man a plan a canal Panama')

-- Table-Value Functions
-- clrGetStrings

SELECT * FROM dbo.clrGetStrings('Test')
SELECT * FROM dbo.clrGetStrings('Red,Blue,Green')

-- Assign to table variable

SET NOCOUNT ON

DECLARE @Table Table (Value NVARCHAR(20))
INSERT @Table
  SELECT * FROM dbo.clrGetStrings('Red,Blue,Green')
SELECT * FROM @Table

-----------------------------------------------------------------------------
-- Stored Procedures
-----------------------------------------------------------------------------

-- Testing

exec clrSimpleProcedure

-- Show SQL Server Profiler here!

-- Test other SqlPipe.Send options

EXEC clrGetVersion

EXEC clrGetEmployees

EXEC clrGetCustomRecord

-- Test parameters

EXEC clrInputStringParamProcedure

EXEC clrInputStringParamProcedure 'Richard'

DECLARE @Name nvarchar(4000)
EXECUTE [AdventureWorks].[dbo].[clrOutputStringParamProcedure] @Name OUTPUT
SELECT @Name

DECLARE @Name nvarchar(4000)
SET @Name = 'Richard'
EXECUTE [AdventureWorks].[dbo].clrInputOutputStringParamProcedure 
   @Name OUTPUT
SELECT @Name

DECLARE @Return int
EXECUTE @Return = [AdventureWorks].[dbo].[clrReturnValueProcedure]
SELECT @Return

-----------------------------------------------------------------------------
-- Debugging
-----------------------------------------------------------------------------

SELECT dbo.clrCoolEncrypt('SQL Server 2005 Rocks!')

-----------------------------------------------------------------------------
-- User-Defined Type
-----------------------------------------------------------------------------

-- DROP TABLE AuctionItem

-- Create Table, using the new UDT

CREATE TABLE AuctionItem (
  ID    int,
  Item  nvarchar(50),
  Price Currency)
GO

-- Add some items

INSERT AuctionItem VALUES (1,'Toaster','50 USD')
INSERT AuctionItem VALUES (2,'Microwave Oven','30 EUR')
INSERT AuctionItem VALUES (3,'Blender','65 ILS')

-- Test

SELECT Item,
       Price.Amount as Amount,
       Price.CurrencyCode as CurrencyCode
  FROM AuctionItem

-----------------------------------------------------------------------------
-- User-Defined Aggregate
-----------------------------------------------------------------------------

-- How do find the most expensive item?

SELECT MAX(Price) FROM AuctionItem

-- Conversion Web Service: http://localhost/currencyws/Service.asmx

ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON
GO

-- Add our Serialization Assembly

-- DROP ASSEMBLY [CoolDbObjectsXML]

CREATE ASSEMBLY [CoolDbObjectsXML]
  FROM 'C:\Demo\Seminar\CoolDBObjects\bin\Debug\CoolDBObjects.XmlSerializers.dll'
  WITH PERMISSION_SET = SAFE
GO

-- Try our new function

SELECT dbo.MAXCURRENCY(Price) FROM AuctionItem

-- Add a more expensive item

INSERT AuctionItem VALUES (4,'Espresso Maker','55 EUR')
SELECT dbo.MAXCURRENCY(Price) FROM AuctionItem

-- Done

DROP TABLE AuctionItem
DROP ASSEMBLY [CoolDbObjectsXML]